/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cbsrs.controller;

import com.cbsrs.model.Colleges;
import com.cbsrs.model.ProgramRevisions;
import com.cbsrs.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author Arnold
 */
public class ProgramRevisionDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(ProgramRevisionDAO.class);

    public static List<ProgramRevisions> findAll(int collegeId) throws Exception {
        List<ProgramRevisions> programRevisionList = new ArrayList<ProgramRevisions>();
        String sql =
                "select a.program_revision_id, a.program_revision_name,a.program_revision_description,\n"
                + "a.program_revision_duration, b.program_id, b.program_name, b.program_description,\n"
                + "b.college_id, b.user_id, b.create_ts\n"
                + "from program_revisions a\n"
                + "	LEFT JOIN	\n"
                + "		programs b\n"
                + "	on a.program_id = b.program_id\n"
                + "where b.college_id=?";
        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, collegeId);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        ProgramRevisions programRevision;
        while (rs.next()) {
            programRevision = new ProgramRevisions();
            programRevision.setProgramRevisionID(rs.getInt("program_revision_id"));
            programRevision.setProgramRevisionName(rs.getString("program_revision_name"));
            programRevision.setProgramRevisionDescription(rs.getString("program_revision_description"));
            programRevision.setProgramRevisionDuration(rs.getInt("program_revision_duration"));
            programRevision.setProgramID(rs.getInt("program_id"));
            programRevision.setProgramName(rs.getString("program_name"));
            programRevision.setProgramDescription(rs.getString("program_description") + " - " + rs.getString("program_revision_description") + " ("+rs.getString("program_revision_name")+")");
            programRevision.setCollegeID(rs.getInt("college_id"));
            programRevision.setUserID(rs.getInt("user_id"));
            programRevision.setCreateTs(rs.getString("create_ts"));
            programRevisionList.add(programRevision);
            
        }
        closeConnection(getConnectionObject());
        return programRevisionList;
    }
}
